Create Dictionary Tables in ODS Database and Populate the Dictionary Tables 10

Download the Sql File attached to the   

Run Script that is attached to create your ODS tables in the ODS Database. Once ran the tables will be made up of these columns.



        
    
    

After creating the Dictionary tables in ODS Database- use SSIS to populate the Dictionary tables using the OLDB_Source as source.  To load the clean data into the Dictionary tables - used Derived Column Transformation, Sort and Data Conversion Transformation to load the Staging Files into OLDB_Destination ie Dictionary tables


To load Dictionary Agency:




AgencyName == "NYC FIRE PENSION FUND" ? 257 : AgencyName == "ADMIN FOR CHILDRE
N'S SVCS" ? 67 : AgencyName == "CULTURAL AFFAIRS" ? 126 : AgencyName == "GUTTMAN 
COMMUNITY COLLEGE" ? 462 : AgencyName == "HOUSING PRESERVATION & DVLPMNT" ? 806 : AgencyName == "HRA/DEPT OF SOCIAL SERVICES" ? 69 : AgencyName == "ADMIN TRIALS AND HEARINGS" ? 820 : AgencyName == "BOARD OF CORRECTION" ? 73 : AgencyName == "BOARD OF ELECTION" ? 3 : AgencyName == "BOROUGH PRESIDENT-BRONX" ? 11 : AgencyName == "BOROUGH PRESIDENT-BROOKLYN" ? 12 : AgencyName == "BOROUGH PRESIDENT-QUEENS" ? 13 : AgencyName == "BOROUGH PRESIDENT-STATEN IS" ? 14 : AgencyName == "BRONX DISTRICT ATTORNEY" ? 902 : AgencyName == "BUSINESS INTEGRITY COMMISSION" ? 831 : AgencyName == "CITY CLERK" ? 103 : AgencyName == "CITY COUNCIL" ? 102 : AgencyName == "CIVILIAN COMPLAINT REVIEW BD" ? 54 : AgencyName == "COMMUNITY COLLEGE (BRONX)" ? 463 : AgencyName == "COMMUNITY COLLEGE (HOSTOS)" ? 468 : AgencyName == "COMMUNITY COLLEGE (KINGSBORO)" ? 465 : AgencyName == "COMMUNITY COLLEGE (LAGUARDIA)" ? 469 : AgencyName == "COMMUNITY COLLEGE (MANHATTAN)" ? 466 : AgencyName == "CONSUMER AFFAIRS" ? 866 : AgencyName == "DEPARTMENT FOR THE AGING" ? 125 : AgencyName == "DEPARTMENT OF BUILDINGS" ? 810 : AgencyName == "DEPARTMENT OF BUSINESS SERV." ? 801 : AgencyName == "DEPARTMENT OF CITY PLANNING" ? 30 : AgencyName == "DEPARTMENT OF CORRECTION" ? 72 : AgencyName == "DEPARTMENT OF EDUCATION ADMIN" ? 740 : AgencyName == "DEPARTMENT OF FINANCE" ? 836 : AgencyName == "DEPARTMENT OF INVESTIGATION" ? 32 : AgencyName == "DEPARTMENT OF PROBATION" ? 781 : AgencyName == "DEPARTMENT OF SANITATION" ? 827 : AgencyName == "DEPARTMENT OF TRANSPORTATION" ? 841 : AgencyName == "DEPT OF CITYWIDE ADMIN SVCS" ? 868 : AgencyName == "DEPT OF ED PEDAGOGICAL" ? 742 : AgencyName == "DEPT OF ENVIRONMENT PROTECTION" ? 826 : AgencyName == "DEPT OF HEALTH/MENTAL HYGIENE" ? 816 : AgencyName == "DEPT OF INFO TECH & TELECOMM" ? 858 : AgencyName == "DEPT OF PARKS & RECREATION" ? 846 : AgencyName == "DEPT OF RECORDS & INFO SERVICE" ? 860 : AgencyName == "DEPT OF YOUTH & COMM DEV SRVS" ? 261 : AgencyName == "DEPT. OF DESIGN & CONSTRUCTION" ? 850 : AgencyName == "DEPT. OF HOMELESS SERVICES" ? 71 : AgencyName == "DISTRICT ATTORNEY KINGS COUNTY" ? 903 : AgencyName == "DISTRICT ATTORNEY QNS COUNTY" ? 904 : AgencyName == "DISTRICT ATTORNEY RICHMOND COU" ? 905 : AgencyName == "FINANCIAL INFO SVCS AGENCY" ? 127 : AgencyName == "FIRE DEPARTMENT" ? 57 : AgencyName == "HUMAN RIGHTS COMMISSION" ? 226 : AgencyName == "LANDMARKS PRESERVATION COMM" ? 136 : AgencyName == "LAW DEPARTMENT" ? 25 : AgencyName == "NYC DEPT OF VETERANS' SERVICES" ? 63 : AgencyName == "NYC HOUSING AUTHORITY" ? 996 : AgencyName == "OFFICE OF EMERGENCY MANAGEMENT" ? 17 : AgencyName == "OFFICE OF LABOR RELATIONS" ? 214 : AgencyName == "OFFICE OF MANAGEMENT & BUDGET" ? 19 : AgencyName == "OFFICE OF THE ACTUARY" ? 8 : AgencyName == "OFFICE OF THE COMPTROLLER" ? 15 : AgencyName == "OFFICE OF THE MAYOR" ? 2 : AgencyName == "POLICE DEPARTMENT" ? 56 : AgencyName == "PRESIDENT BOROUGH OF MANHATTAN" ? 10 : AgencyName == "PUBLIC ADVOCATE" ? 101 : AgencyName == "TAX COMMISSION" ? 21 : AgencyName == "TAXI & LIMOUSINE COMMISSION" ? 156 : AgencyName == "TEACHERS RETIREMENT SYSTEM" ? 41 : PayrollNumber


To load Dictionary Employee:  below Query used as Source 


Query:

select * from VW_DictionaryEmployee

Derived Column:


To load Dictionary LeaveStatus: 




Data Conversion:


Derived Column Code:



To load Dictionary Location:




Derived Column Code:


To Load Dictionary Title:



Derived Column Code:


Replace Code for Title Description and Length:

REPLACE(REPLACE(REPLACE(TitleDescription_60,"*",""),"?*",""),"?","")

LEFT(TitleDescription,60)


To load Job Status:  below Query used as Source 



Query: 

select  [PayBasis] from [dbo].[Tbl_Stg_NYCPayroll]

Derived Column Code:



Code for PayBasis:

PayBasis == "per Hour" ? "Seasonal" : PayBasis == "per Day" ? "Temporary" : "Permenant"


To load Dictionary Salary:

Download the Sql File attached to the  




Derived Column Code:


Code for Job Status:

PayBasis == "per Hour" ? "Seasonal" : PayBasis == "per Day" ? "Temporary" : "Permenant"


Data Conversion:


Execute the package to populate the ODS tables with the data.

Creating views in SQL Server

https://www.sqlshack.com/create-view-sql-creating-views-in-sql-server/